package com.zhiyou.util;

import com.zhiyou.entity.PowerCenter;
import com.zhiyou.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author sanshi
 * @desc
 * @create 2021-09-01 15:17
 */
public class DBUtil {
    //建立连接
    Connection connection = null;
    Statement st = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void getConn(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/anhui_news?serverTimezone=UTC";
            String userName = "root";
            String passWord = "root";
            connection = DriverManager.getConnection(url, userName, passWord);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //登录
    public boolean login(String sql,Object[] params){
        boolean f=false;
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length ; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()){
                f=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn();
            return f;
        }
    }
    //得到所有用户信息
    public ArrayList<User> getAllUser() {
        ArrayList<User> list = new ArrayList<User>();
        String sql = "select * from sys_user";
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User(rs.getString("acct"), rs.getString("pwd"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.closeConn();
        return list;
    }
    //对数据的增删改操作
    public int updateInfo(String sql,Object[] params){
        int r = 0;
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length ; i++) {
                ps.setObject(i+1,params[i]);
            }
            r = ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeConn();
            return r;
        }
    }
    //获取总条数
    public int getTotalCount(String sql){
        int totalCount = 0;
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                totalCount = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.closeConn();
        return totalCount;
    }
    //查找数据是否存在
    public boolean isExit(String sql,Object[] params){
        this.getConn();
        boolean flag = false;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length ; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()){
              flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.closeConn();
        return flag;
    }

    //条件查询
    public List<PowerCenter> searchByCondition(String sql, Object[] params){
        ArrayList<PowerCenter> list = new ArrayList<PowerCenter>();
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length ; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                PowerCenter powerCenter = new PowerCenter(rs.getString("acct"), rs.getString("chnl_code"), rs.getString("sys_code"));
                list.add(powerCenter);
            }
        } catch (SQLException e) {
                e.printStackTrace();
        }
        this.closeConn();
        return list;
    }
    //释放连接
    public void closeConn(){
        try {
            if (ps!=null)
                ps.close();
            if (rs!=null) {
                rs.close();
            }
            if (st!=null){
                st.close();
            }
            if (connection!=null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
